package mysql;

import java.sql.Connection;
import java.sql.DriverManager;
//import java.sql.PreparedStatement;
import java.sql.SQLException;
//import java.util.ArrayList;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.ArrayList;

import javax.swing.JOptionPane;

public  class RequeteSql {

	/**
	 * @param args
	 */
	private static  Connection conn = null;
	private static Statement stmt = null;
	private static int idNumbon;
	
	
	/**
	 * Permet de commence une connection JDBC 
	 * sans configuration necessite les parametre suivant
	 * localhost     ex: "localhost:3306"
	 * user ex       = "root"
	 * passwd ex      = "prince"
	 */
	
	public static boolean TAP_InitialiserConnectionJDBC(String url, String port, String user, String passwd, String baseName)
	{
		try {
			conn = DriverManager.getConnection("jdbc:mysql://"+ url + ":"+ port + "/" + baseName, user, passwd);
			
			
			} catch (SQLException ex)
			{
				// handle any errors
				System.out.println("Verifier le nom, mot de passe et nom de la base de donne et le pilote");
				//JOptionPane.showMessageDialog( "ERROR", "PROBLEME BASE DE DONNE");
				JOptionPane.showMessageDialog(null, "Ce n'est qu'un simple message",
					      "avertissement",
					      JOptionPane.WARNING_MESSAGE);
				System.out.println("SQLException: " + ex.getMessage());
				//System.out.println("SQLState: " + ex.getSQLState());
				//System.out.println("VendorError: " + ex.getErrorCode());
				return (false);
			}
		return (true);
	}
	
	/*
	 * Permet de commence une connection JDBC 
	 * avec les configuration de l ordi de tapha c a dire 
	 * localhost:3306
	 * user = "root"
	 * passwd = ""
	 */
	
	public static boolean TAP_InitialiserConnectionJDBC()
	{
		String url = "jdbc:mysql://localhost:3306/ciprovis";
		String user = "root";
		String passwd = "";
		
		try {
			conn = DriverManager.getConnection(url, user, passwd);
			
			
			} catch (SQLException ex)
			{
				JOptionPane.showMessageDialog(null,"VERIFIER URL USER ET PASSWORD", "ERREUR BASE",  JOptionPane.ERROR_MESSAGE);
				System.out.println("TAPSQLException: VERIFIER URL USER ET PASSWORD");
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
				return (false);
			}
		return (true);
	}
	
	
	public static boolean TAP_VerfierConnectionJDBC(){
		
	   boolean isconnected=false;
	   
		try {
			
			isconnected=conn.isClosed();
			
			
			   
		} catch (SQLException e) {
			
			isconnected=true;
		}
		
       return !isconnected;		
	}
	
	
	

	public static boolean TAP_closeConnection()
	{
		if (conn != null)
		{
			try
			{
				conn.close();
			} catch (Exception e)
			{
				System.out.println("Erreur fermeture de connection");
				return (false);
				// TODO: handle exception
			}
		}
		return true;
		
	}
		
	
	private static  int exectInsert(String req, Boolean trace)
	{
		
		int lastKey = -1;
		stmt = null;
		
		
		try 
		{
			stmt = conn.createStatement();
			stmt.executeUpdate(req, Statement.RETURN_GENERATED_KEYS);
			ResultSet keys = stmt.getGeneratedKeys();
		    
		    while (keys.next()) {
		      lastKey = keys.getInt(1);
		    }
			if (trace == true)
			{
				if (lastKey != -1)
				{
					System.out.println("INSERTION CORRECTE" + lastKey);
				}
				else
				{
					System.out.println("ERREUR DURANT L INSERTION OU PAS DE CLE AUTO_INCREMENT");
					
				}
				}
			
		}
		catch (SQLException ex)
			{
				// handle any errors
				System.out.println("SQLRequete: " + req); 
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
			}
		finally
		{
		
			 if (stmt != null)
			 {
			 try {
				 stmt.close();
			 	} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			  }
			 }
		}
		return lastKey;
		
		
	}
	
	
	/*
	 * execute une requete select 
	 * prend en parametre la requete 
	 * trace est un boolean pour specifier si vous voulez voir les logs
	 */
	
	private static  Object[][] exectSelect(String req, Boolean trace)
	{
		ResultSetMetaData resultMeta;
		ResultSet result = null;
		stmt = null;
		int ligne, colonne;
		Object obARetourner[][] = null;
		try 
		{
			stmt = conn.createStatement();
			result = stmt.executeQuery(req);
			resultMeta =  result.getMetaData();
			colonne = resultMeta.getColumnCount();
			result.last();
			//on r�cup�re le num�ro de la ligne
			ligne = result.getRow();
			//on repace le curseur avant la premi�re ligne
			result.beforeFirst();
		    obARetourner = new Object[ligne][colonne];
			if (trace == true)
			{
				System.out.println("\n**********************************");
				//On affiche le nom des colonnes
				for (int i = 1; i <=  resultMeta.getColumnCount(); i++)
				{
					System.out.print("\t" + resultMeta.getColumnName(i).toUpperCase() + "\t *");
				}
					System.out.println("\n**********************************");
					int j = 0;
					while(result.next())
					{			
						for(int i = 1, k = 0; i <=  resultMeta.getColumnCount(); i++, k++)
						{
							if (result.getObject(i) != null)
							{
								obARetourner[j][k] = result.getObject(i);
								System.out.print("\t" + result.getObject(i).toString() + "\t |");
							}
						}
						j++;
						System.out.println("\n---------------------------------");
					}
				// Now do something with the ResultSet ....
			}
			else
			{
				int j = 0;
				while(result.next())
				{			
					for(int i = 1; i <=  resultMeta.getColumnCount(); i++)
					{
						obARetourner[j][i - 1] = result.getObject(i);
					}
	   			   j++;
				}
			}
		}
		catch (SQLException ex)
			{
				// handle any errors
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("Votre requete est : " + req);
			}
		finally
		{
		 if (result != null)
	    	 {
			   try
			   {
				result.close();
			   }
			   catch (SQLException e) 
			   {
				// TODO Auto-generated catch block
		    		e.printStackTrace();
			   }
			 }
			 if (stmt != null)
			 {
			 try {
				 stmt.close();
			 	} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			  }
			 }
		}
		
		return obARetourner;
	}
	

	//--------------------------------------------------------------------------
	/*
	 * Avoir tous les champs
	 */
	
	/**
	 * Cette methode permet de faire une recherche dans la bdd par rapport au mot qui lui
	 * est pass� en parramettre.
	 * @param duc_mot
	 * @return  elle renvoie une ArrayList de themes
	 * @throws ConnectionBDException
	 *
	 */
/*		public static ArrayList<Theme> DUC_listeThemeSearch(String duc_mot) 
		{
			PreparedStatement prStat = null;
			ArrayList<Theme> duc_listing = new ArrayList<Theme>(); /**@ variable qui contient liste de theme  
			
			//Connection connect = ConnectionBD.getConnection();/**@ Connexion a la bdd 
			try {	
				/**
				 * On ajoute les % % autour du mot afin que la la close LIKE soit au bon format
				 
				duc_mot ="%"+duc_mot+"%"; 
				String sql = "SELECT * FROM lexique WHERE abreviation LIKE ? OR nom LIKE ?"; // requete sql vers la bdd
				prStat = conn.prepareStatement(sql);
				prStat.setString(1, duc_mot);
				prStat.setString(2, duc_mot);
				ResultSet rs = prStat.executeQuery();// resutset contenant le r�sultat de la requete
				
			   while (rs.next()){
				   
					int duc_id = rs.getInt("id_lexique");
					String duc_abrev = rs.getString("abreviation");
					String duc_nom = rs.getString("nom");
					String duc_description = rs.getString("description");
					
					Theme duc_theme = new Theme(duc_id, duc_abrev, duc_nom, duc_description);//instanciation de l'objet th�me
							
					duc_listing.add(duc_theme);
				}
				
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}		
			
	       //Fermeture de la connexion
			
			finally{
				if(prStat!=null)
					try{
						prStat.close();
					}catch(SQLException e){
						e.printStackTrace();
					}
			}
			
			return duc_listing;
		}

	*/

	
	
	
	
	//------------------------------------------------------------------------------------------------
	
	
	
	/*
		
	private  Object[][] TAP_getToutChampEnFonctionWhere(String nomchamp, String valeurWhere, Boolean trace)
	{
		
			String sql = "SELECT * FROM `lexique` WHERE `" + nomchamp + "` = " + valeurWhere;
			return (TAP_exectSelect(sql, trace));
			
	}
	
	*/
	
	
	
	// update champ executeUpdate
	private static  int exectUpdate(String req)
	{
				int res = -1;
		
		ResultSet result = null;
		stmt = null;
		
		try 
		{
			stmt = conn.createStatement();
			res = stmt.executeUpdate(req);
			
		
		}
		catch (SQLException ex)
			{
				System.out.println("requete :" + req);
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
			}
		finally
		{
		 if (result != null)
	    	 {
			   try
			   {
				result.close();
			   }
			   catch (SQLException e) 
			   {
				// TODO Auto-generated catch block
		    		e.printStackTrace();
			   }
			 }
			 if (stmt != null)
			 {
			 try {
				 stmt.close();
			 	} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			  }
			 }
		}
		return res;
	}
	
	
	
	
	/*GAYE
	 * 
	 * 
	 * 
	 * 
	 * 
	 */
	public static Object[][] nbrProduitAacheter()
	{
		String sql = " SELECT p.reference,p.designation, SUM(nb_clients_formule(f.CODEFORMULE)* nb_produits_dans_formule(p.reference, f.CODEFORMULE))as 'NBR A ACHETER' , p.QUANTITE_STOCK" +
					 " FROM produit p, formule f, contientproduit c "  +
					 " WHERE p.reference=c.reference " +
					 " and f.CODEFORMULE=C.CODEFORMULE " +
					 " GROUP BY (p.reference) ";
		System.out.println(sql);
		return exectSelect(sql, true);
	}
	
	
	public static Object[][] nbrClientEnFoncFormule()
	{
		String sql = "SELECT COUNT( c.numeroclient ) AS 'nbr clients', f.nomformule , f.codeformule" +
					" FROM client c, formule f, souscription s" +
					" WHERE c.numeroclient = s.numeroclient" +
					" AND s.codeformule = f.codeformule" +
					" GROUP BY (" +
					" f.codeformule" +
					")";
		return exectSelect(sql, true);
	}
	
	
	
	
	public static Object[][] produitQuantiteStock(int idProduit)
	{
		return null;
			
	}
	public static Object[][] livraisonSelectFonctionSous(String numClient, String refProduit)
	{
		String sql = " SELECT l.numlivraison, l.datelivraison, l.adresselivraison, al.quantite, p.designation, c.nom " +
					 " FROM livraison l, articlelivre al, produit p, client c, souscription s " +
					 " WHERE al.reference = p.reference" + 
					 " AND al.numlivraison = l.numlivraison" +
					 " AND l.numero_sous = s.numero_sous" +
					 " AND s.numeroclient = c.numeroclient" +
					 " AND c.numeroclient = " + numClient +
					 " AND l.statut = 1" +
					 " AND p.reference = " + refProduit;
				System.out.println(sql);
				return (exectSelect(sql, false));
	}
	
	public static Object[][] livraisonSelectFonctionSous2(String numClient, String refProduit,String nomprod)
	{
		String sql = " SELECT l.numlivraison, l.datelivraison, l.adresselivraison, al.quantite, p.designation, c.nom " +
					 " FROM livraison l, articlelivre al, produit p, client c, souscription s " +
					 " WHERE al.reference = p.reference" + 
					 " AND al.numlivraison = l.numlivraison" +
					 " AND l.numero_sous = s.numero_sous" +
					 " AND s.numeroclient = c.numeroclient" +
					 " AND c.numeroclient = " + numClient +
					 " AND l.statut = 1" +
					 " AND p.reference = " + refProduit+
					 " AND p.DESIGNATION='"+nomprod+"'";
				System.out.println(sql);
				return (exectSelect(sql, false));
	}
	
	
	public static Object[][] produitSelectFonctionSous(String codeformule, String refProduit)
	{
		String sql = "SELECT c.quantite, p.designation " +
				" FROM formule f, contientproduit c, produit p " +
				" WHERE c.codeformule = f.codeformule " +
				" AND c.reference = p.reference " +
				" AND f.codeformule = " + codeformule +
				" AND p.reference = " + refProduit + "  AND f.STATUT = 1 AND p.STATUT = 1";
		System.out.println(sql);
		return (exectSelect(sql, true));
	}
	
	public static Object[][] articleLivreRestantEnFonctionDumois(String numSous, String refProduit, String date)
	{	
		String req2 = "Select SUM(ac.quantite), l.datelivraison " +
				" FROM livraison l, articlelivre ac, produit p, souscription s " +
				" WHERE l.numlivraison = ac.numlivraison " +
				" AND p.reference = ac.reference " +
				" AND s.numero_sous = l.numero_sous " +
				" AND s.numero_sous = " + numSous +
				" AND p.reference = " + refProduit +
				" AND  DATE_FORMAT(l.datelivraison, '%m') = DATE_FORMAT('" + date + "', '%m')" +
			    " AND l.STATUT = 1";
		System.out.println(req2);

		return exectSelect(req2, true);
	}
	
	
	public static int articleLivreInsert(String numLivraison, String quantite, String produitId)
	{	
		String req2 = "INSERT INTO `ciprovis`.`articlelivre` (`NUMLIVRAISON` ,`REFERENCE` ,`QUANTITE`) " +
						"VALUES ('" + numLivraison + "', '" + produitId + "', '" + quantite + "')";

		int a = exectInsert(req2, true);

		return a;
	}
	
	public static int livraisonInsert(String clientId,  String date, String addresse)
	{
		int numSous = RequeteSql.getNumClientPerRapportSous(clientId);
		String req1 = " INSERT INTO `ciprovis`.`livraison` (`NUMLIVRAISON` ,`NUMERO_SOUS` ,`DATELIVRAISON`  ,`ADRESSELIVRAISON`) "+
					  " VALUES (NULL , '" + numSous + "', '" + date + "', '" + addresse + "')";
		
		int numLivraison = exectInsert(req1, true);
		
		return numLivraison;
	}
	
	public static Object [][] produitSelect()
	{
		String req;
		
		req = " SELECT * FROM produit  WHERE STATUT = 1";
		return (exectSelect(req, false));
	}
	
	
	public static Object [][] produitSelectEnFonctionFormule(int obInfoClient)
	{
		String req;
		
		req = " SELECT p.* , cp.quantite " +
				" FROM produit p, contientproduit cp, formule f "+
				" WHERE cp.reference = p.reference " +
				" AND cp.codeformule = f.codeformule " +
				" AND f.codeformule =" + obInfoClient + " AND p.STATUT = 1";
		return (exectSelect(req, true));
	}
	
	
	public static int getNumClientPerRapportSous(String idclient)
	{
		String sql = "SELECT NUMERO_SOUS " +
					  " FROM `souscription` " + 
					  " WHERE `NUMEROCLIENT` = " + idclient;
		
		System.out.println(sql);
		Object[][] ob = exectSelect(sql, true);
		return (Integer)ob[0][0];
	}
	
	public static Object[][] payementSelect(String idClient)
	{
		int id = getNumClientPerRapportSous(idClient);
		String sql = "SELECT * FROM `paiemenet` WHERE NUMERO_SOUS = " + id + "  AND STATUT = 1";
		return (exectSelect(sql, true));
	}
	
	public static Object[][] payementSelectPerMoisPerClient(String mois)
	{
		
		String req = "SELECT CONCAT( CONCAT( c.nom, \" \" ) , c.prenom ) AS nom, p.montant, p.datepaiement, p.numpaiement, z.nom, c.`NUMEROCLIENT` , z.zone_id, z.DESCRIPTION, c.ADRESSE, (" +
					  " SELECT SUM( p.montant )" +
					  " FROM client c, paiemenet p, souscription s, `zone_location` z " +
					  " WHERE p.statut =1 " +
						" AND c.statut =1 " +
						" AND p.numero_sous = s.numero_sous " +
						" AND c.zone_id = z.zone_id " +
						" AND s.numeroclient = c.numeroclient " +
						" AND p.mois_regle = '" + mois + "'" +
					")" +
					" FROM client c, paiemenet p, souscription s, `zone_location` z " +
					" WHERE p.statut =1" +
					" AND c.statut =1" +
					" AND p.numero_sous = s.numero_sous" +
					" AND c.zone_id = z.zone_id" +
					" AND s.numeroclient = c.numeroclient" +
					" AND p.mois_regle = '" + mois + "'";
		
		System.out.println(req);
		
		return (exectSelect(req, true));
	}
	
	
	
	public static int paiementInsert(String idclient, String montant, String date, String moisRegle, String anneRegle)
	{
		int numsous = getNumClientPerRapportSous(idclient);
		String sql = "INSERT INTO `ciprovis`.`paiemenet` (`NUMPAIEMENT` ,`NUMERO_SOUS` ,`MONTANT` ,`DATEPAIEMENT`, `MOIS_REGLE`, `ANNE_REGLE`) " +
				" VALUES (NULL , '"+ numsous + "', '" + montant + "', '" + date + "', '" + moisRegle + "', '" + anneRegle + "');";
		return (exectInsert(sql, true));
	}
	
	public static int paiementUpdate(String idpayement, String montant, String date)
	{
		
		String sql = "UPDATE `ciprovis`.`paiemenet` SET `MONTANT` = '" + montant + ",`DATEPAIEMENT` = '" + date +
				" WHERE `paiemenet`.`NUMPAIEMENT` = " + idpayement;
		return (exectUpdate(sql));
	}

	
	public static Object[][] clientListPerLigne(String id)
	{
		String req = "SELECT  c.NUMEROCLIENT, c.NOM, c.PRENOM, c.ADRESSE, c.TELEPHONE, f.NOMFORMULE, s.DATESOUS, f.CODEFORMULE, s.NUMERO_SOUS , z.nom, z.zone_id, z.DESCRIPTION " +
				     " FROM `client` c, `formule` f, `souscription` s , `zone_location` z" +
				     " WHERE s.`NUMEROCLIENT` = c.NUMEROCLIENT " +
				     " AND c.zone_id = z.zone_id" +
				     " AND s.`CODEFORMULE` = f.CODEFORMULE " +
				     " AND c.`NUMEROCLIENT` = " + id + " AND c.STATUT = 1";	
		System.out.println(req);
		System.out.println();
		Object ob[][] =  exectSelect(req, true);
		return ob;
		
	}
	
	public static Object[][] clientListPerLigneNonFiltrer()
	{
		String req = "SELECT  c.NUMEROCLIENT, c.NOM, c.PRENOM, c.ADRESSE, c.TELEPHONE, f.NOMFORMULE, s.DATESOUS, f.CODEFORMULE, s.NUMERO_SOUS, z.nom " +
				     "FROM `client` c, `formule` f, `souscription` s , `zone_location` z " +
				     "WHERE s.`NUMEROCLIENT` = c.NUMEROCLIENT " + 
				     "AND s.`CODEFORMULE` = f.CODEFORMULE " +
				     " AND z.zone_id = c.zone_id" + 
				     " AND c.STATUT = 1";	
		System.out.println(req);
		System.out.println();
		Object ob[][] =  exectSelect(req, true);
		return ob;
		
	}
	
	public static void clientDeletePerTel(String tel)
	{
		String sql = "UPDATE `ciprovis`.`client` SET `STATUT` = '-1' WHERE `client`.`tel` = '" + tel + "'";
		
		exectUpdate(sql);
	}
	
	/*
	public static Object[][] clientListPerLigneNonFiltrerTrier(String filtre, String associeFiltre, String ordre, Boolean croissance, String search)
	{
		String recherche = search.trim();
		String req = "SELECT  c.NUMEROCLIENT, c.NOM, c.PRENOM, c.ADRESSE, c.TELEPHONE, f.NOMFORMULE, s.DATESOUS, f.CODEFORMULE, s.NUMERO_SOUS, z.nom " +
				     "FROM `client` c, `formule` f, `souscription` s , `zone_location` z " +
				     "WHERE s.`NUMEROCLIENT` = c.NUMEROCLIENT " + 
				     "AND s.`CODEFORMULE` = f.CODEFORMULE " +
				     " AND z.zone_id = c.zone_id " + 
				     " AND c.STATUT = 1";
		if ((filtre != null) && (associeFiltre != null))
		{
			if (filtre.compareToIgnoreCase("z.ZONE_ID") == 0)
			{
				int ob =  clientZoneIDFonctionID(associeFiltre);
				req += " AND " + filtre + " = '" + ob + "' ";
			}
			else
			{
				req += " AND " + filtre + " = " + associeFiltre;
			}
		}
		if (recherche.trim().compareToIgnoreCase("") != 0)
		{
			req += " AND (c.NOM LIKE \"%"  + recherche + "%\" OR c.ADRESSE LIKE \"%"  + recherche + "%\" OR c.TELEPHONE LIKE \"%"  + recherche + "%\" )"; 
		}
		if (ordre != null)
		{
			req += " ORDER BY(" + ordre + ") ";
		}
		if (croissance == true)
		{
			req += "ASC";
		}
		else
		{
			req += "DESC";
		}
		System.out.println(req);
		System.out.println();
		
		Object ob[][] =  exectSelect(req, true);
		return ob;
		
	}
	*/
	public static Object[][] clientListPerLigneNonFiltrerTrier(String filtre, String associeFiltre, String ordre, Boolean croissance, String search, String formule)
	{
		String recherche = search.trim();
		String req = "SELECT  c.NUMEROCLIENT, c.NOM, c.PRENOM, c.ADRESSE, c.TELEPHONE, f.NOMFORMULE, s.DATESOUS, f.CODEFORMULE, s.NUMERO_SOUS, z.nom " +
				     "FROM `client` c, `formule` f, `souscription` s , `zone_location` z " +
				     "WHERE s.`NUMEROCLIENT` = c.NUMEROCLIENT " + 
				     "AND s.`CODEFORMULE` = f.CODEFORMULE " +
				     " AND z.zone_id = c.zone_id " + 
				     " AND c.STATUT = 1";
		if ((filtre != null) && (associeFiltre != null))
		{
			if (filtre.compareToIgnoreCase("z.ZONE_ID") == 0)
			{
			
				req += " AND z.nom = '" + associeFiltre + "' ";
			}
			
		}
		if (formule != null)
		{
			req += " AND f.NOMFORMULE = '" + formule + "' ";
		}
		if (recherche.compareToIgnoreCase("") != 0)
		{
			req += " AND (c.NOM LIKE \"%"  + recherche + "%\" OR c.ADRESSE LIKE \"%"  + recherche + "%\" OR c.TELEPHONE LIKE \"%"  + recherche + "%\" )"; 
		}
		if (ordre != null)
		{
			req += " ORDER BY(" + ordre + ") ";
		}
		if (croissance == true)
		{
			req += "ASC";
		}
		else
		{
			req += "DESC";
		}
		System.out.println(req);
		System.out.println();
		
		Object ob[][] =  exectSelect(req, true);
		return ob;
	}
	
	
	public Object[][] nbrClientFonctionFormule()
	{
		String req = "SELECT COUNT(s.numeroclient), f.nomformule " +
					" FROM souscription s, client c, formule f " +
					" WHERE s.numeroclient = c.numeroclient " +
					" AND c.statut = 1" +
					" AND f.codeformule = s.codeformule " +
					" group by s.codeformule ";
		System.out.println(req);
		Object ob[][] =  exectSelect(req, true);
		return ob;
	}
	
	public static boolean clientExist(String tel)
	{
		String req = "SELECT * FROM `client` WHERE STATUT = 1 AND TELEPHONE='" + tel + "'";	
		Object ob[][] =  exectSelect(req, true);
		System.out.println(req);
		if (ob.length == 0)
		return false;
		else
			return true;
	}
	

	public static Object[][] clientList()
	{
		String req = "SELECT * FROM `client` WHERE STATUT = 1";	
		System.out.println(req);
		Object ob[][] =  exectSelect(req, true);
		return ob;
		
	}
	
	public static Object[][] clientSelectFormule()
	{
		String req = "SELECT NOMFORMULE FROM `formule` WHERE STATUT = 1";	
		Object ob[][] =  exectSelect(req, true);
		return ob;
		
	}
	
	
	
	
	
	public static int clientrecupCodeFormule(String nomformule)
	{
		String req = "SELECT CODEFORMULE FROM `formule` WHERE NOMFORMULE = '" + nomformule + "' AND  STATUT = 1";	
		Object ob[][] =  exectSelect(req, true);
	
		return (Integer) ob[0][0];
		
	}
	
	public static int clientZoneIDFonctionID(String nomZone)
	{
		String req = "SELECT ZONE_ID FROM `zone_location` WHERE NOM  = '" + nomZone + "' AND  STATUT = 1";	
		System.out.println(req);
		Object ob[][] =  exectSelect(req, true);
	
		return (Integer) ob[0][0];
		
	}
	
	public static boolean clientRemplieChamp(String nom, String prenom, String numero, String formule, String date, String addresse, String zone)
	{
		int idzone = clientZoneIDFonctionID(zone);
		String req = "INSERT INTO `ciprovis`.`client` (`NUMEROCLIENT`, `NOM`, `PRENOM` , `TELEPHONE` , `ADRESSE`, `ZONE_ID`) VALUES (NULL, '" + nom + "', '" + prenom + "',  '" + numero + "','"+ addresse + " ',' "+ idzone +" ')";	
		int id =  exectInsert(req, true);
		System.out.println(req);
		if (id == -1)
		{
			return (false);
		}
		int formuleId = clientrecupCodeFormule(formule);
		req = "INSERT INTO `ciprovis`.`souscription` (`NUMERO_SOUS`, `NUMEROCLIENT`, `CODEFORMULE`, `DATESOUS`) VALUES (NULL, '"+ id + "', '" + formuleId + "', '" + date + "')";
		System.out.println(req);
		exectInsert(req, true);
		if (formuleId == -1)
		{
			return (false);
		}
		return true;
		
	}
	
	public static boolean zoneRemplieChamp( String nom, String desc)
	{
		String req = "INSERT INTO `ciprovis`.`zone_location` (`zone_id`, `nom`, `description`) VALUES (NULL, '" + nom + "', '" + desc + "')";	
		
		if (exectInsert(req, true) != -1)
		
		return true;
		else
			return (false);
		
	}
	
	public static Object[][] zoneSelect()
	{
		String req = "SELECT * 	FROM `zone_location`";	
		Object ob[][] =  exectSelect(req, true);
		return ob;
		
	}
	
	
	public static boolean clientUpdate(String nom, String prenom, String numero, String formule, String date, String addresse, String id, String zone)
	{
		String sql = "UPDATE `ciprovis`.`client` " +
					 "SET `NOM` = '" + nom + "'," +
					 "`PRENOM` = '" + prenom + " ', " +
					 "`ADRESSE` = '" + addresse + "', " +
					 "`TELEPHONE` = '" + numero +
					 "' WHERE `client`.`NUMEROCLIENT` = '" + id + "'";
		
		int formuleId = clientrecupCodeFormule(formule);
		
		String sql2 = "UPDATE `ciprovis`.`souscription`" +
				" SET `CODEFORMULE`  = '" + formuleId + "'," +
				"`DATESOUS` = '" + date + "'" +
				" WHERE `souscription`.`NUMEROCLIENT` = " + id;
		
		
		int zoneId = clientZoneIDFonctionID(zone);
		
		String sql3 = "UPDATE `ciprovis`.`client`" +
				" SET `zone_id`  = '" + zoneId + "'" +
				" WHERE `client`.`NUMEROCLIENT` = " + id;
		
		
		 System.out.println(sql   + "\n\n" + sql2 + "\n\n" + sql3 + "\n\n");
		int res = exectUpdate(sql);
		
	   if ((res != 0) && (res != -1))
	   {
		   res = exectUpdate(sql2);
		   
		   if ((res != 0) && (res != -1))
		   {
			   res = exectUpdate(sql3);
			  
			   if ((res != 0) && (res != -1))
			   {
				   
				   return (true);
			   }
		   }
	   }
	   
		return false;
	}
	
	
	
	
	public static void clientDelete(String clientclique)
	{
		String sql = "UPDATE `ciprovis`.`client` SET `STATUT` = '-1' WHERE `client`.`NUMEROCLIENT` = " + clientclique;
		System.out.println(sql);
		exectUpdate(sql);
	}
	

	
	
	
	
	/***
	 * KANE Version 1.0 Creation des methodes sql
	 * 
	 ***/

	public static Object[][] produitSelectDesignation() {
		String req = "SELECT DESIGNATION FROM `produit` WHERE STATUT = 1";
		Object ob[][] = exectSelect(req, false);
		return ob;

	}

	public static Object[][] produitSelectREFERENCE() {
		String req = "SELECT REFERENCE FROM `produit`  WHERE STATUT = 1";
		Object ob[][] = exectSelect(req, false);
		return ob;

	}

	public static boolean bonEntreeRempliChamps(String numBon,
			String numFournis, String dateEntree) {
		String req = "INSERT INTO `ciprovis`.`bonentree` (`NUMBON`, `NUMEROFOURN`, `DATEBONENTREE`) VALUES (NULL,'"
				+ numFournis + "','" + dateEntree + " ')";
		idNumbon = exectInsert(req, true);
		

		return true;

	}

	public static int entreeRempliChamps(String numEntree, String reference,
			String numBon, String prixAchat, String quantite) {
		numBon = String.valueOf(idNumbon);
		String req = "INSERT INTO `ciprovis`.`entree` (`NUMENTREE`, `REFERENCE`, `NUMBON`, `PRIX_ACHAT`,`QUANTITE`)"
				+ " VALUES (NULL,'"
				+ reference
				+ "','"
				+ numBon
				+ " ','"
				+ prixAchat + " ','" + quantite + " ')";
		int id = exectInsert(req, true);
		

		return id;

	}

	public static int UpdateStockProduit(int res, String designation) {
		String req = "UPDATE produit SET QUANTITE_STOCK=" + res
				+ " WHERE DESIGNATION='" + designation + "'";
		int result = exectUpdate(req);
		return result;

	}

	public static Object[][] selectQuantiteProduitForpRODnAME(String designation) {
		String req = "SELECT QUANTITE_STOCK FROM `produit` WHERE DESIGNATION ='"
				+ designation + "'  AND STATUT = 1";
		Object ob[][] = exectSelect(req, false);
		return ob;

	}

	public static Object[][] selectDesignationName() {
		String req = "SELECT DESIGNATION FROM `produit` WHERE STATUT = 1";
		Object ob[][] = exectSelect(req, false);
		return ob;

	}

	public static Object[][] selectQuantiteProduit() {
		String req = "SELECT QUANTITE_STOCK FROM `produit` WHERE STATUT = 1";
		Object ob[][] = exectSelect(req, false);
		return ob;

	}

	public static Object[][] selectFournisseurID() {
		String req = "SELECT NUMEROFOURN FROM `fournisseur`   WHERE STATUT = 1";
		Object ob[][] = exectSelect(req, false);
		return ob;

	}

	public static Object[][] selectFournisseurName() {
		String req = "SELECT NOM FROM fournisseur WHERE STATUT=1";
		Object ob[][] = exectSelect(req, false);
		return ob;

	}

	public static Object[][] selectFournisseurIdByName(String nomF) {
		String req = "SELECT NUMEROFOURN FROM `fournisseur` WHERE NOM ='"
				+ nomF + "' AND STATUT = 1";
		Object ob[][] = exectSelect(req, false);
		return ob;

	}

	public static Object[][] fournisseurList() {
		String req = "SELECT * FROM fournisseur  WHERE STATUT = 1 ";
		Object ob[][] = exectSelect(req, true);
		return ob;

	}

	public static boolean fournisseurUpdate(String nom, String adress,
			String email, String tel, String id) {
		String sql = "UPDATE `ciprovis`.`fournisseur` " + "SET `NOM` = '" + nom
				+ "'," + "`ADRESSE` = '" + adress + " ', " + "`EMAIL` = '"
				+ email + "', " + "`TELEPHONE` = '" + tel
				+ "' WHERE `fournisseur`.`NUMEROFOURN` = '" + id + "'";
		exectUpdate(sql);
		return false;

	}

	public static Object[][] fournisseurListPerLigne(String id) {
		String req = "SELECT NOM,ADRESSE,EMAIL,TELEPHONE  FROM fournisseur WHERE `NUMEROFOURN` = "
				+ id + " AND STATUT = 1";
		System.out.println(req);
		Object ob[][] = exectSelect(req, true);
		return ob;

	}

	public static boolean fournisseurRempliChamps(String numF, String nomF,
			String adressF, String emailF, String telF) {
		String req = "INSERT INTO `ciprovis`.`fournisseur` (`NUMEROFOURN`, `NOM`, `ADRESSE`, `EMAIL`, `TELEPHONE`) VALUES (NULL,'"
				+ nomF
				+ "','"
				+ adressF
				+ " ','"
				+ emailF
				+ " ','"
				+ telF
				+ " ')";
		idNumbon = exectInsert(req, true);
		System.out.println(idNumbon);

		return true;

	}

	public static Object[][] selectFournisseurNameByName(String nomF) {
		String req = "SELECT NOM FROM `fournisseur` WHERE NOM ='" + nomF + "' AND STATUT = 1";
		Object ob[][] = exectSelect(req, false);
		return ob;

	}

	public static boolean fournisseurDeleteLogique(String statut, String nom) {
		String sql = "UPDATE `ciprovis`.`fournisseur` SET `STATUT` = " + statut
				+ " WHERE `fournisseur`.`NOM` = '" + nom + "'";
		exectUpdate(sql);
		return false;

	}

	public static Object[][] produitList() {
		String req = "SELECT * FROM produit  WHERE STATUT = 1 ";
		Object ob[][] = exectSelect(req, true);
		return ob;

	}

	public static boolean produitUpdate(String designation, String pu,
			String qt, String id) {
		String sql = "UPDATE `ciprovis`.`produit` " + "SET `DESIGNATION` = '"
				+ designation + "'," + "`PRIX_UNITAIRE` = " + pu + " , "
				+ "`QUANTITE_STOCK` = " + qt + " "
				+ " WHERE `produit`.`REFERENCE` = '" + id + "'";
		exectUpdate(sql);
		System.out.println(sql);
		return false;

	}

	public static boolean produitDeleteLogique(String statut, String nom) {
		String sql = "UPDATE `ciprovis`.`produit` SET `STATUT` = " + statut
				+ " WHERE `produit`.`DESIGNATION` = '" + nom + "'";
		exectUpdate(sql);
		return false;

	}

	public static Object[][] produitListPerLigne(String id) {
		String req = "SELECT DESIGNATION,PRIX_UNITAIRE ,QUANTITE_STOCK FROM produit WHERE `REFERENCE` = "
				+ id + " AND STATUT = 1";
		System.out.println(req);
		Object ob[][] = exectSelect(req, true);
		return ob;

	}

	public static boolean produitRempliChamps(String numP, String nomF,
			String puP, String qtP) {
		String req = "INSERT INTO `ciprovis`.`produit` (REFERENCE,`DESIGNATION`, `PRIX_UNITAIRE`,QUANTITE_STOCK) VALUES (NULL,'"
				+ nomF + "'," + puP + "," + qtP + ")";
		idNumbon = exectInsert(req, true);
		System.out.println(req);
		//System.out.println(idNumbon);

		return true;

	}

	public static Object[][] selectProduitNameByName(String nomF) {
		String req = "SELECT DESIGNATION  FROM `produit` WHERE DESIGNATION  ='"
				+ nomF + "'  AND STATUT = 1";
		Object ob[][] = exectSelect(req, false);
		return ob;

	}

	public static Object[][] selectProdIdByName(String nomF) {
		String req = "SELECT REFERENCE FROM `produit` WHERE DESIGNATION  ='"
				+ nomF + "'  AND STATUT = 1";
		Object ob[][] = exectSelect(req, false);
		System.out.println(req);
		return ob;

	}
	
	public static ArrayList<Object[][]> selectProdIdByNameList(ArrayList<String> nomF) {
		ArrayList<Object[][]> retourList =new ArrayList<Object[][]>();
		for (int i = 0; i < nomF.size(); i++) {
			String req = "SELECT REFERENCE FROM `produit` WHERE DESIGNATION  ='"
					+ nomF + "'  AND STATUT = 1";
			Object ob[][] = exectSelect(req, false);
			retourList.add(ob);
		}
		
		return retourList;

	}

	public static Object[][] formuleList() {
		String req = "SELECT * FROM formule  WHERE STATUT <> -1  ORDER BY NOMFORMULE";
		Object ob[][] = exectSelect(req, true);
		return ob;

	}

	public static Object[][] formuleListPerLigne(String id) {
		String req = "SELECT *  FROM formule WHERE `CODEFORMULE` = " + id + " AND STATUT = 1";
		System.out.println(req);
		Object ob[][] = exectSelect(req, true);
		return ob;

	}
	

	public static Object[][] selectProdContientProduitByCodeFormule(
			String idFormule) {
		String req = "SELECT contientproduit.quantite FROM contientproduit,produit,formule WHERE "
				+ "contientproduit.codeformule=formule.codeformule and contientproduit.reference=produit.reference and contientproduit.codeformule  ="
				+ idFormule + " AND formule.STATUT = 1";
		Object ob[][] = exectSelect(req, false);
		return ob;

	}
	
	public static int formuleRempliChamps(String codeformule, String nomformule,
			String description, int tarif_mens, int tarif_inscr) {
		
		String req = "INSERT INTO `ciprovis`.`formule` (`CODEFORMULE`, `NOMFORMULE`, `DESCRIPTION`, `TARIF_MENSUEL`,`TARFIF_INSCRIPTION`)"
				+ " VALUES (NULL,'"
				+ nomformule
				+ "','"
				+ description
				+ "',"
				+ tarif_mens + " ," + tarif_inscr + ")";
		int id = exectInsert(req, true);
		System.out.println(id);

		return id;
	}
	
	public static Object[][] selectFormuleNameByName(String nomF) {
		String req = "SELECT NOMFORMULE FROM `formule` WHERE NOMFORMULE ='" + nomF + "'  AND STATUT = 1";
		Object ob[][] = exectSelect(req, false);
		return ob;

	}
	public static Object[][] selectFormuleIdByName(String nomF) {
		String req = "SELECT CODEFORMULE FROM `formule` WHERE NOMFORMULE ='"
				+ nomF + "'  AND STATUT = 1";
		Object ob[][] = exectSelect(req, false);
		System.out.println(req);
		return ob;

	}
	
	public static boolean formuleDeleteLogique(String statut, String nom) {
		String sql = "UPDATE `ciprovis`.`formule` SET `STATUT` = " + statut
				+ " WHERE `formule`.`NOMFORMULE` = '" + nom + "'";
		exectUpdate(sql);
		return false;

	}
	public static int contientProduitRempliChamps(int codeformule, int reference,int quantite) {
		String req = "INSERT INTO `ciprovis`.`contientproduit` (`CODEFORMULE`, `REFERENCE`, `QUANTITE`)"
				+ " VALUES ("+codeformule+","
				+ reference
				+ ","
				+ quantite + ")";
		int id = exectInsert(req, true);
		
		System.out.println(req);

		return id;
	}
	
	public static boolean formuleUpdate(String nom, String description,
			String tarif_mensuel, String tarif_inscr, String id) {
		String sql = "UPDATE `ciprovis`.`formule` " + "SET `NOMFORMULE` = '" + nom
				+ "'," + "`DESCRIPTION` = '" + description + " ', " + "`TARIF_MENSUEL` = "
				+ tarif_mensuel + ", " + "`TARFIF_INSCRIPTION` = " + tarif_inscr
				+ " WHERE `formule`.`CODEFORMULE` = '" + id + "'";
		exectUpdate(sql);
		return false;

	}
	public static boolean contientProduitUpdate(int quantite, int idCodeForm, int idReference) {
		String sql = "UPDATE `ciprovis`.`contientproduit` " + "SET `QUANTITE` = " + quantite+"" +
				" WHERE `contientproduit`.`CODEFORMULE` = " + idCodeForm + " AND `contientproduit`.`REFERENCE` ="+idReference + "";
		exectUpdate(sql);
		return false;

	}
	public static Object[][] entreeListe() {
		String req = "SELECT * FROM `entree` WHERE STATUT = 1";
		Object ob[][] = exectSelect(req, true);
		return ob;

	}
	
	

	/***
	 * KANE
	 * Version 1.1
	 * Creation des methodes sql
	 *  
	 ***/
	
	
	public static Object[][] clientSelectByID(String id)
	{
		String req = "SELECT c.NOM,c.ADRESSE,f.NOMFORMULE, z.nom" +
				     " FROM `client` c, `formule` f, `souscription` s , `zone_location` z" +
				     " WHERE s.`NUMEROCLIENT` = c.NUMEROCLIENT " +
				     " AND c.zone_id = z.zone_id" +
				     " AND s.`CODEFORMULE` = f.CODEFORMULE " +
				     " AND c.`NUMEROCLIENT` = " + id + " AND c.STATUT = 1";	
		System.out.println(req);
		System.out.println();
		Object ob[][] =  exectSelect(req, true);
		return ob;
		
	}
	//blannana
}
	
	
	

	
	
	
